package cn.com.callback;

import cn.com.elite.EliteBean
import cn.com.elite.Util;
import cn.com.kh.KHDBConfig;
import cn.com.kh.KHUtil;
import net.sf.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory
import org.springframework.jdbc.core.JdbcTemplate;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @Title: apiKFCustIssued*
 * @Description: 客服促开任务自动下发线程*
 * @author: taohao*
 * @date 2021-12-06 23:58:55
 *
 */

public class apiKFCustIssued implements ServiceBackInvoke {
    private static final Logger log = LoggerFactory.getLogger(apiKFCustIssued.class);
    private static String API_OuterDomain = null;
    private static String WXCallBackURL = null;
    private static JdbcTemplate jt = null;

    String doCallBack(EliteBean eliteBean, HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse) {
        httpServletResponse.setCharacterEncoding("UTF-8");
        httpServletResponse.setContentType("application/json;charset=utf-8");

        API_OuterDomain = KHDBConfig.getAPIURL(eliteBean, "API-InnerDomain");
        WXCallBackURL = KHDBConfig.getAPIURL(eliteBean, "WXCallBackURL") + "/ngsapitask_pa/file/apiFileProxy?fileurl=" + KHDBConfig.getAPIURL(eliteBean, "NGS-InnserDomain") + "fs/get?file=";
        jt = new JdbcTemplate(eliteBean.getDataSource());

        // 查询待处理名单  0是为分配的名单
        List accountList = eliteBean.chaxun_sql("Select Rec_Id,Customerid,task_type, task_guid, createtime, deal_flag,deal_result,Deal_Time,mobile,appointment_time,channel,channels,f_cloud_state,opendate,f_register_time,f_operation_step,f_last_operation_time,innerentry From Account_Information a where  a.deal_flag='0' and task_type='CK_01'  and NOW()-INTERVAL 120 HOUR < a.createtime  order by  a.appointment_time  asc  ", "dataSource");

        for (int i = 0; i < accountList.size(); i++) {
            long startTime = System.currentTimeMillis();
            Map accountMap = accountList.get(i);
            String Rec_Id = accountMap.get("Rec_Id");//处理任务主键
            String task_type = accountMap.get("task_type");//任务类型 需拿到客服的促开任务的名单
            String mobile = accountMap.get("mobile");//手机号码
            String appointment_time = accountMap.get("appointment_time");//预约时间
            String channel = accountMap.get("channel");//渠道id
            String channels = accountMap.get("channels");//细分渠道id
            String zz = accountMap.get("Customerid");//资金账号
            String f_cloud_state = accountMap.get("f_cloud_state");//云平台状态
            String zz1 = accountMap.get("opendate");//开户时间
            String f_register_time = accountMap.get("f_register_time");//注册时间
            String f_operation_step = accountMap.get("f_operation_step");//操作步骤
            String f_last_operation_time = accountMap.get("f_last_operation_time");//操作时间
            String innerentry=accountMap.get("innerentry");//预约来源
            log.debug("资金账号：" + zz + " 手机号码：" + mobile + " 开户时间:" + zz1+"innerentry:"+innerentry);
            //查询Customer 表是否存在改促开客户
            List custList = eliteBean.chaxun_sql("select Customer_Guid from customer where MOBILE='" + mobile + "' limit 1", "dataSource");
            //存在则更新预约时间,渠道,细分渠道
            String Customer_Guid=null;
            long endTime = System.currentTimeMillis();
            if (custList.size() > 0) {
                //存在更新入库
                Map custMap = custList.get(0);
                 Customer_Guid = custMap.get("Customer_Guid");
                log.debug("修改已存在mobile:" + mobile+"预约时间："+appointment_time);
                String[] custTaskUpdateSQL = new String[2];
                custTaskUpdateSQL[0] = "update customer set appointment_time ='" + appointment_time + "',channel='" + channel + "',f_xf_channel='" + channels + "',f_cloud_state='" + f_cloud_state + "',open_account='" + zz1 + "',register_time='" + f_register_time + "',STRINGFIELD13='1',f_operation_step='"+f_operation_step+"',f_last_operation_time='"+f_last_operation_time+"' ,innerentry='"+innerentry+"' where Customer_Guid='" + Customer_Guid + "'";
                custTaskUpdateSQL[1] = "Update Account_Information Set Deal_Time = Now(), Deal_Flag = 1, Deal_Result = '更新数据成功-耗时:" + (endTime - startTime) + "' Where Rec_Id='" + Rec_Id + "'";

                int[] batchSQLUpdateResult = eliteBean.updateBatch(custTaskUpdateSQL, "dataSource");
                log.debug("更新数据成功-耗时:" + (endTime - startTime) + "新客户入库批量处理SQL返回结果：" + batchSQLUpdateResult.toString());
            } else {
                //不存在新增入库
                Customer_Guid = "CUST_" +  KHUtil.getRandomString(31).toUpperCase();;
                eliteBean.update("insert into customer(customer_guid,appointment_time,channel,f_xf_channel,mobile,f_cloud_state,open_account,register_time,CREATEDBY,CREATEDDATE,MODIFIEDBY,MODIFIEDDATE,f_operation_step,f_last_operation_time,innerentry)values('" + Customer_Guid + "','" + appointment_time + "','" + channel + "','" + channels + "','" + mobile + "','" + f_cloud_state + "','" + zz1 + "','" + f_register_time + "','SELITE',now(),'SELITE',now(),'"+f_operation_step+"','"+f_last_operation_time+"','"+innerentry+"')", "dataSource");
            }





            //查找Objective 表是否有同任务类型
            List ObjCountList = eliteBean.chaxun_sql("select Customer_Guid,HANDLEGROUP_ID,HANDLEBY_ID FROM Objective WHERE MAP_ID='4CA2PP' and CUSTOMER_GUID='" + Customer_Guid + "' ", "dataSource");
            if (ObjCountList.size() <= 0) {
                //新任务类型新增入库
                String Rule_Id = null;
                String r_Control_Id = null;
                String r_Rolegroup_Id = null;
                String r_Staff_Id = null;

                //先查询企微关系人对应关系表  20240911
               String qwListSQL=''' Select q.mobile,q.staff_id,q.Weixinid,q.staffname from qw_relevance_list q left join staff s on s.staff_id=q.staff_id where q.mobile =? and s.workstatus='ONLINE'   order by  q.Createddate desc  Limit 1 ''';
               List<Map<String, Object>> qwList = jt.queryForList(qwListSQL,mobile);

                //先查询是否存在自动任务断点名单 20250724
                List DXCountList = eliteBean.chaxun_sql("select Customer_Guid,HANDLEGROUP_ID,HANDLEBY_ID FROM Objective WHERE MAP_ID='4G3CCS' and CUSTOMER_GUID='" + Customer_Guid + "' ", "dataSource");

                if (qwList.size()>0){
                    //查到有企微对应关系人
                    r_Control_Id ='';
                    r_Rolegroup_Id =KHDBConfig.getParam(eliteBean,"DX_ZB");
                    r_Staff_Id =qwList.get(0).get("staff_id");
                    log.debug("[查询到企微对应当前下发人信息] Rolegroup_Id:" + r_Rolegroup_Id + " Staff_Id:" + r_Staff_Id);
                }else if(DXCountList.size()>0){
                  r_Rolegroup_Id = qwList.get(0).get("HANDLEGROUP_ID");
                  r_Staff_Id = qwList.get(0).get("HANDLEBY_ID");
                  log.debug("[查询到电销自动任务对应当前下发人信息] Rolegroup_Id:" + r_Rolegroup_Id + " Staff_Id:" + r_Staff_Id);
              }else if(DXCountList.size()>0){
                     r_Rolegroup_Id = DXCountList.get(0).get("HANDLEGROUP_ID");
                     r_Staff_Id = DXCountList.get(0).get("HANDLEBY_ID");
                    log.debug("[查询到电销自动任务对应当前下发人信息] Rolegroup_Id:" + r_Rolegroup_Id + " Staff_Id:" + r_Staff_Id);

                }else{

                // 通过名单信息中渠道在规则表中找到优先级最高的规则
                String ruleCheckSQL = "Select Rule_Id, Rule_Name, Rolegroup, Staff_Id, Order_Adjustment,yy_endttime,yy_starttime From Hst_Rule_Issue Where Rule_State = 1 And Find_In_Set('" + channel + "', Code_Id) > 0  and  Date_Format('"+appointment_time+"', '%H:%i:%s') >=yy_starttime  and  Date_Format('"+appointment_time+"', '%H:%i:%s') <=yy_endttime    and department_flag=2 Order By Order_Adjustment Limit 1";
                List ruleCheckList = eliteBean.chaxun_sql(ruleCheckSQL, "dataSource");
                if (ruleCheckList.size() > 0) {
                    Map ruleCheckMap = ruleCheckList.get(0);
                    Rule_Id = ruleCheckMap.get("Rule_Id");
                    String Rule_Name = ruleCheckMap.get("Rule_Name");
                    String Rolegroup = ruleCheckMap.get("Rolegroup");
                    String Staff_Id = ruleCheckMap.get("Staff_Id");
                    String Order_Adjustment = ruleCheckMap.get("Order_Adjustment");
                    String yy_starttime = ruleCheckMap.get("yy_starttime")== null ? '' : ruleCheckMap.get("yy_starttime");
                    String yy_endttime = ruleCheckMap.get("yy_endttime")== null ? '' : ruleCheckMap.get("yy_endttime");
                    log.debug("[输出获取规则信息] 规则ID:" + Rule_Id + " 规则名:" + Rule_Name + " 下发组:" + Rolegroup + " 下发人:" + Staff_Id + " 优先级:" + Order_Adjustment);
                    // 初始化规则下发
                    ruleAssignInit(eliteBean, Rule_Id, channel,appointment_time, yy_starttime,yy_endttime);
                    // 取当前名单下发最少的坐席（不支持一人多组）
                    String getAssginStaffSQL = "Select R1.Rec_Id, R2.Rolegroup_Id, R1.Staff_Id" +
                            "  From Hst_Rule_Issue_Control R1" +
                            "  Left Join Staffrolegroup R2" +
                            "    On R1.Staff_Id = R2.Staff_Id" +
                            "  Left Join Staff R3" +
                            "    On R1.Staff_Id = R3.Staff_Id" +
                            " Where Rule_Id = " + Rule_Id + "" +
                            "   And Assign_Date = Date_Format(Now(), '%Y-%m-%d')" +
                            "   And channel='" + channel + "'  " +
                            "   And R2.Rolegroup_id='" + Rolegroup + "' " +
                            "   And  Date_Format('"+appointment_time+"', '%H:%i:%s') >=R1.yy_starttime " +
                            "   And  Date_Format('"+appointment_time+"', '%H:%i:%s')<=R1.yy_endttime "+
                            "   Order By Assign_Num,Staff_Id Limit 1";
                    List getAssginStaffList = eliteBean.chaxun_sql(getAssginStaffSQL, "dataSource");
                    if (getAssginStaffList.size() > 0) {
                        Map getAssginStaffMap = getAssginStaffList.get(0);
                        r_Control_Id = getAssginStaffMap.get("Rec_Id");
                        r_Rolegroup_Id = getAssginStaffMap.get("Rolegroup_Id");
                        r_Staff_Id = getAssginStaffMap.get("Staff_Id");
                        log.debug("[当前名单下发人信息] Rolegroup_Id:" + r_Rolegroup_Id + " Staff_Id:" + r_Staff_Id);

                    } else {
                        r_Rolegroup_Id = '';
                        r_Staff_Id = '';
                        log.debug("[当前名单下发人信息] 无组别人员 默认不分配 进入未分配数据池！");
                    }

                } else {
                    //走规则兜底下发
                    Map ddmap = NullChannelRule(eliteBean,channel,appointment_time);
                    r_Control_Id = ddmap.get("r_Control_Id");
                    r_Rolegroup_Id = ddmap.get("r_Rolegroup_Id");
                    r_Staff_Id = ddmap.get("r_Staff_Id");
                    log.debug("[当前名单兜底下发人信息] Rolegroup_Id:" + r_Rolegroup_Id + " Staff_Id:" + r_Staff_Id+"r_Control_Id"+r_Control_Id);
                }
                }



                //判断当前规则维护 组人是否有值 没有则进入未分配池 存在则按规则分配
                List OBList = eliteBean.chaxun_sql("select Customer_guid,Objective_Guid from Objective where customer_guid='" + Customer_Guid + "' and MAP_ID='4CA2PP'", "dataSource");
                //如果没有则继续执行
                if (OBList.size() <= 0 || OBList == null) {
                    if ((r_Rolegroup_Id == '' || r_Rolegroup_Id == null) && (r_Staff_Id == '' || r_Staff_Id == null)) {
                        //新客户入库 进入未分配池
                        newCustomerDeal(eliteBean, Rec_Id, Rule_Id, r_Control_Id, r_Rolegroup_Id, r_Staff_Id, task_type, Customer_Guid, mobile, appointment_time, channel, channels,innerentry);
                    } else {
                        //绑定成功 新客户入库
                        boolean newCustDeal = newCustomerDeal(eliteBean, Rec_Id, Rule_Id, r_Control_Id, r_Rolegroup_Id, r_Staff_Id, task_type, Customer_Guid, mobile, appointment_time, channel, channels,innerentry);
                        if (newCustDeal) {
                            // 更新规则下发记录
                            endTime = System.currentTimeMillis();
                            String updateCustSQL = "Update Account_Information Set Deal_Time = Now(), Deal_Flag = 1, Deal_Result = '新建下发成功-耗时:" + (endTime - startTime) + "' Where Rec_Id='" + Rec_Id + "'";
                            log.debug("下发成功-耗时:" + (endTime - startTime));
                            eliteBean.update(updateCustSQL, "dataSource");
                        } else {
                            endTime = System.currentTimeMillis();
                            String updateCustSQL = "Update Account_Information Set Deal_Time = Now(), Deal_Flag = -1, Deal_Result = '新建下发失败-耗时:" + (endTime - startTime) + "' Where Rec_Id='" + Rec_Id + "'";
                            eliteBean.update(updateCustSQL, "dataSource");
                            log.warn("新建客户&任务失败-耗时:" + (endTime - startTime));
                        }
                    }
                } else {
                    //如存在则返回失败
                    endTime = System.currentTimeMillis();
                    String updateCustSQL = "Update Account_Information Set Deal_Time = Now(), Deal_Flag = -3, Deal_Result = '任务类型已存在-耗时:" + (endTime - startTime) + "' Where Rec_Id='" + Rec_Id + "'";
                    eliteBean.update(updateCustSQL, "dataSource");
                    log.warn("新建任务失败该客户已经有同类型的任务-耗时:" + (endTime - startTime));
                }
            } else {
                if (ObjCountList.size() > 0) {
                    Map ObjCountMap = ObjCountList.get(0);
                    String r_Rolegroup_Id = ObjCountMap.get("HANDLEGROUP_ID");
                    String r_Staff_Id = ObjCountMap.get("HANDLEBY_ID");
                    //判断当前处理人员是否离职
                    List WorkStatusList=eliteBean.chaxun_sql("select WORKSTATUS From staff  where WORKSTATUS ='QUIT'   and staff_id='"+r_Staff_Id+"' limit 1 ","dataSource");
                    //不在电销组的人员
                    List NoDxList=eliteBean.chaxun_sql("select WORKSTATUS From staff  s left join staffrolegroup srg on s.staff_id=srg.staff_id where s.staff_id='"+r_Staff_Id+"' and srg.ROLEGROUP_ID ='LMGVGI' limit 1  ","dataSource");
                    if (WorkStatusList.size()>0||NoDxList.size()<=0){
                        //把离职人员的任务均分给当前配置的人员
                        String Rule_Id = null;
                        String r_Control_Id = null;
                        // 通过名单信息中渠道在规则表中找到优先级最高的规则
                        String ruleCheckSQL = "Select Rule_Id, Rule_Name, Rolegroup, Staff_Id, Order_Adjustment,yy_starttime,yy_endttime From Hst_Rule_Issue Where Rule_State = 1 And Find_In_Set('" + channel + "', Code_Id) > 0  and  Date_Format('"+appointment_time+"', '%H:%i:%s') >=yy_starttime  and  Date_Format('"+appointment_time+"', '%H:%i:%s') <=yy_endttime   and department_flag=2 Order By Order_Adjustment Limit 1";
                        List ruleCheckList = eliteBean.chaxun_sql(ruleCheckSQL, "dataSource");
                        if (ruleCheckList.size() > 0) {
                            Map ruleCheckMap = ruleCheckList.get(0);
                            Rule_Id = ruleCheckMap.get("Rule_Id");
                            String Rule_Name = ruleCheckMap.get("Rule_Name");
                            String Rolegroup = ruleCheckMap.get("Rolegroup");
                            String Staff_Id = ruleCheckMap.get("Staff_Id");
                            String yy_starttime = ruleCheckMap.get("yy_starttime")== null ? '' : ruleCheckMap.get("yy_starttime");;
                            String yy_endttime = ruleCheckMap.get("yy_endttime")== null ? '' : ruleCheckMap.get("yy_endttime");;
                            String Order_Adjustment = ruleCheckMap.get("Order_Adjustment");
                            log.debug("[输出获取规则信息] 规则ID:" + Rule_Id + " 规则名:" + Rule_Name + " 下发组:" + Rolegroup + " 下发人:" + Staff_Id + " 优先级:" + Order_Adjustment);
                            // 初始化规则下发
                            ruleAssignInit(eliteBean, Rule_Id, channel,appointment_time, yy_starttime,yy_endttime);
                            // 取当前名单下发最少的坐席（不支持一人多组）
                            String getAssginStaffSQL = "Select R1.Rec_Id, R2.Rolegroup_Id, R1.Staff_Id" +
                                    "  From Hst_Rule_Issue_Control R1" +
                                    "  Left Join Staffrolegroup R2" +
                                    "    On R1.Staff_Id = R2.Staff_Id" +
                                    "  Left Join Staff R3" +
                                    "    On R1.Staff_Id = R3.Staff_Id" +
                                    " Where Rule_Id = " + Rule_Id + "" +
                                    "   And Assign_Date = Date_Format(Now(), '%Y-%m-%d')" +
                                    "   And  Date_Format('"+appointment_time+"', '%H:%i:%s')>=R1.yy_starttime " +
                                    "   And  Date_Format('"+appointment_time+"', '%H:%i:%s')<=R1.yy_endttime "+
                                    "   And channel='" + channel + "'  " +
                                    "   And R2.Rolegroup_id='" + Rolegroup + "' " +
                                    " Order By Assign_Num,Staff_Id Limit 1";
                            List getAssginStaffList = eliteBean.chaxun_sql(getAssginStaffSQL, "dataSource");
                            if (getAssginStaffList.size() > 0) {
                                Map getAssginStaffMap = getAssginStaffList.get(0);
                                r_Control_Id = getAssginStaffMap.get("Rec_Id");
                                r_Rolegroup_Id = getAssginStaffMap.get("Rolegroup_Id");
                                r_Staff_Id = getAssginStaffMap.get("Staff_Id");
                                log.debug("[当前名单下发人信息] Rolegroup_Id:" + r_Rolegroup_Id + " Staff_Id:" + r_Staff_Id);
                            } else {
                                r_Rolegroup_Id = '';
                                r_Staff_Id = '';
                                log.debug("[当前名单下发人信息] 无组别人员 默认不分配 进入未分配数据池！");
                            }
                        } else {
                            //走规则兜底下发
                            Map ddmap = NullChannelRule(eliteBean,channel,appointment_time);
                            r_Control_Id = ddmap.get("r_Control_Id");
                            r_Rolegroup_Id = ddmap.get("r_Rolegroup_Id");
                            r_Staff_Id = ddmap.get("r_Staff_Id");
                            log.debug("[当前名单兜底下发人信息] Rolegroup_Id:" + r_Rolegroup_Id + " Staff_Id:" + r_Staff_Id+"r_Control_Id"+r_Control_Id);
                        }

                        //开始更换人员
                        if ((r_Rolegroup_Id != '' && r_Rolegroup_Id != null) && (r_Staff_Id != '' && r_Staff_Id != null)) {
                            //新客户入库 进入未分配池
                            Boolean updateDeal= UpdateStaff(eliteBean,r_Control_Id,r_Rolegroup_Id, r_Staff_Id, Customer_Guid,channel);
                            if (updateDeal) {
                                // 更新规则下发记录
                                endTime = System.currentTimeMillis();
                                String updateCustSQL = "Update Account_Information Set Deal_Time = Now(), Deal_Flag = 1, Deal_Result = '更新客户经理成功-耗时:" + (endTime - startTime) + "' Where Rec_Id='" + Rec_Id + "'";
                                log.debug("更换成功-耗时:" + (endTime - startTime));
                                eliteBean.update(updateCustSQL, "dataSource");
                            } else {
                                endTime = System.currentTimeMillis();
                                String updateCustSQL = "Update Account_Information Set Deal_Time = Now(), Deal_Flag = -1, Deal_Result = '更换客户数据失败-耗时:" + (endTime - startTime) + "' Where Rec_Id='" + Rec_Id + "'";
                                eliteBean.update(updateCustSQL, "dataSource");
                                log.warn("新建客户&任务失败-耗时:" + (endTime - startTime));
                            }
                        }
                    }
                    //发个短信

                    apimeesg(eliteBean, Customer_Guid, r_Staff_Id, r_Rolegroup_Id, mobile, channel, channels);
                }

            }



        }
        return 1;
    }

    /**
     * 规则下发数据初始化
     */
    public static boolean ruleAssignInit(EliteBean eliteBean, String Rule_Id, String channel,String appointment_time,String yy_starttime,String yy_endttime) {
        boolean reFlag = true;
        //新增判断坐席B岗接任时间的逻辑截止状态、及离职人员默认不分配
        //A.-如果有离职人员 则直接关闭其分配状态-- 是否开启自动分配 0开启 1关闭
        String updatestaffstate1SQL = '''Update Staff
                                           Set Auto_Allocation = 1
                                         Where Auto_Allocation = 0
                                           And Workstatus != 'ONLINE\'''';
        eliteBean.update(updatestaffstate1SQL, "dataSource");

        //B.-如果有B岗角色接任时间到期 则更新企状态为自动开启分配 +清空时间维护
        String updatestaffstate2SQL = '''Update Staff
                                           Set Auto_Allocation = 0, Starttime = Null, Endtime = Null
                                         Where Auto_Allocation = 1
                                           And Starttime < Now()
                                           And Endtime < Now()''';
        eliteBean.update(updatestaffstate2SQL, "dataSource");

        // 初始化（1~4）下发记录表（这里默认按每天清零）
        // 1-查找当前规则配置的坐席
        String ruleStaffSQL = "Select Case" +
                "         When R1.Staff_Id Is Null Or R1.Staff_Id = '' Then" +
                "          R2.Staff_Id" +
                "         Else" +
                "          R1.Staff_Id" +
                "       End As ruleStaff" +
                "  From Hst_Rule_Issue R1" +
                "  Left Join (Select R4.Rule_Id, Group_Concat(Distinct R3.Staff_Id) As Staff_Id" +
                "               From Rolegroup R1" +
                "               Left Join Staffrolegroup R2" +
                "                 On R1.Rolegroup_Id = R2.Rolegroup_Id" +
                "               Left Join Staff R3" +
                "                 On R2.Staff_Id = R3.Staff_Id" +
                "               Left Join Hst_Rule_Issue R4" +
                "                 On Find_In_Set(R1.Rolegroup_Id, R4.Rolegroup) > 0" +
                "              Where R1.Status = 1" +
                "                And ((R3.auto_allocation = 0) " +
                " 				or (R3.auto_allocation = 1 And R3.starttime>now() And R3.endtime>now() And R3.starttime is not null And R3.endtime is not null)" +
                " 				or (R3.auto_allocation = 1 And R3.starttime<now() And R3.endtime<now() And R3.starttime is not null And R3.endtime is not null))" +
                "                And R3.Workstatus = 'ONLINE'" +
                "                And R4.Rule_Id = " + Rule_Id + " and department_flag=2) R2" +
                "    On R1.Rule_Id = R2.Rule_Id" +
                "   And (R1.Staff_Id Is Null Or R1.Staff_Id = '')" +
                " Where R1.Rule_Id = " + Rule_Id + "";
        List ruleStaffList = eliteBean.chaxun_sql(ruleStaffSQL, "dataSource");

        Map ruleStaffMap = ruleStaffList.get(0);
        log.debug("ruleStaffMap:    " + ruleStaffMap);
        String ruleStaff = ruleStaffMap.get("ruleStaff");
        log.debug("ruleStaff:    " + ruleStaff);

        //处理Assign_Num
        def selectassignnumSQL = "select case when count(*)=0 then 0 else  round(sum(assign_num)/count(*)) end as assignnum from hst_rule_issue_control  Where Rule_Id = " + Rule_Id + "  And Assign_Date = Date_Format(Now(), '%Y-%m-%d') and channel='" + channel + "'   And  Date_Format('"+appointment_time+"', '%H:%i:%s') >=Date_Format(yy_starttime, '%H:%i:%s') " +
                "     And  Date_Format('"+appointment_time+"', '%H:%i:%s')<=Date_Format(yy_endttime, '%H:%i:%s')   ";
        def selectassignnumresult = eliteBean.chaxun_sql(selectassignnumSQL, "dataSource");
        def assignnum = 0;
        if (selectassignnumresult.size() > 0) {
            assignnum = selectassignnumresult.get(0).get("assignnum");
        }
        log.debug("assignnum:    " + assignnum);
        // 2-将不在下发规则表中不存在的坐席补充，防止中途规则配置的人或组修改添加
        String insertStaffSQL = "Insert Into Hst_Rule_Issue_Control" +
                "  (Rule_Id, Staff_Id, Assign_Date, Assign_Num,assign_yynum, Last_Check_Time,channel,yy_starttime,yy_endttime)" +
                "  Select " + Rule_Id + ", Staff_Id, Date_Format(Now(), '%Y-%m-%d'), '" + assignnum + "', 0, Now(),'" + channel + "','"+yy_starttime+"','"+yy_endttime+"' " +
                "    From Staff" +
                "   Where Find_In_Set(Staff_Id, '" + ruleStaff + "') > 0" +
                "     And Staff_Id Not In (Select Staff_Id" +
                "                            From Hst_Rule_Issue_Control" +
                "                           Where Rule_Id = " + Rule_Id + "" +
                "                             And Assign_Date = Date_Format(Now(), '%Y-%m-%d') And channel='" + channel + "'    " +
                " And  yy_starttime>='"+yy_starttime+"' " +
                "And   yy_endttime<='"+yy_endttime+"' )";

        int newStaffNum = eliteBean.update(insertStaffSQL, "dataSource");

        log.debug("newStaffNum:" + newStaffNum);

        // 3-将不需要不满足下发的坐席删除，防止中途规则配置的人或组删除
        String deleteStaffSQL = "Delete From Hst_Rule_Issue_Control" +
                " Where Staff_Id Not In" +
                "       (Select Staff_Id" +
                "          From Staff" +
                "         Where Find_In_Set(Staff_Id, '" + ruleStaff + "') > 0" +
                "   And ((auto_allocation = 0) " +
                " 	or (auto_allocation = 1 And starttime>now() And endtime>now() And starttime is not null And endtime is not null)" +
                " 	or (auto_allocation = 1 And starttime<now() And endtime<now() And starttime is not null And endtime is not null))" +
                "   And Workstatus = 'ONLINE')" +
                "   And Rule_Id = " + Rule_Id + "" +
                "   And Assign_Date = Date_Format(Now(), '%Y-%m-%d')";
        int deleteStaffNuM = eliteBean.update(deleteStaffSQL, "dataSource");
        log.debug("newStaffNum:" + deleteStaffNuM);
        return reFlag;
    }
    /**
     * 新客户入库
     */
    public static boolean newCustomerDeal(EliteBean eliteBean, String Rec_Id, String Rule_Id, String r_Control_Id, String r_Rolegroup_Id, String r_Staff_Id, String task_type, String Customer_guid, String mobile, String appointment_time, String channel, String channels,String innerentry) {
        boolean reFlag = true;
        try {
            // 生成相关ID
            String Temp_Guid = KHUtil.getRandomString(31).toUpperCase();
            //   String Customer_Guid = "CUST_" + Temp_Guid;
            String EliteTask_Guid = "TASK_" + Temp_Guid;
            String[] custTaskDealSQL = new String[6];
            String Objective_Guid = "OBJE_" + Temp_Guid;

            // 插入Objective表
            custTaskDealSQL[1] = "Insert Into Objective" +
                    "  (Objective_Guid," +
                    "   Objectivetype_Id," +
                    "   Handleby_Id," +
                    "   Handlegroup_Id," +
                    "   Priority_Id," +
                    "   Expireddate," +
                    "   Customer_Guid," +
                    "   Objectivestatus," +
                    "   Iscontact," +
                    "   Trytimes," +
                    "   Successfulcontacttimes," +
                    "   Createdby," +
                    "   Createddate," +
                    "   Obstatus," +
                    "   Failure_Attempt," +
                    "   Contact_Customer_Guid," +
                    "   Passflag," +
                    "   Lastassigndate," +
                    "   Failstatus," +
                    "   Successstatus," +
                    "   Owner," +
                    "   Workorderframe_Id," +
                    "   Category," +
                    "   Forecastdate," +
                    "   Workorderid," +
                    "   Workordertype1," +
                    "   Workordertype2," +
                    "   Workordertype3," +
                    "   Workordertype4," +
                    "   Objectivesubstatus," +
                    "   Map_Id," +
                    "   Curstep_Id_Map," +
                    "   Max_Attempt," +
                    "   Nocontacttry," +
                    "   Allocation_Pool," +
                    "   Appointment," +
                    "   PA_TASKTYPE)" +
                    "Values" +
                    "  ('" + Objective_Guid + "'," +
                    "   'RE0TX1'," +
                    "   '" + r_Staff_Id + "'," +
                    "   '" + r_Rolegroup_Id + "'," +
                    "   'NORMAL'," +
                    "   Date_Add(Now(), Interval 1 Day)," +
                    "   '" + Customer_guid + "'," +
                    "   'OPEN'," +
                    "   0," +
                    "   0," +
                    "   0," +
                    "   'SELITE'," +
                    "   Now()," +
                    "   'OPEN'," +
                    "   999," +
                    "   ''," +
                    "   0," +
                    "   Null," +
                    "   '0'," +
                    "   '0'," +
                    "   ''," +
                    "   '4CA2PP'," +
                    "   'CASE'," +
                    "   '2021-04-25 17:59:35'," +
                    "   Concat('HS', Date_Format(Now(), '%Y%m%d%H'), '-', Lpad(Floor(Rand() * 10000), 4, '0'))," +
                    "   'QL800E'," +
                    "   'O7JIUV'," +
                    "   ''," +
                    "   ''," +
                    "   'OPEN'," +
                    "   '4CA2PP'," +
                    "   'ONA0KD'," +
                    "   0," +
                    "   0," +
                    "   0," +
                    "   0,'" + task_type + "')";

            // 插入EliteTask表
            custTaskDealSQL[2] = "Insert Into Elitetask" +
                    "  (Elitetask_Guid," +
                    "   Objective_Guid," +
                    "   Objectivetype_Id," +
                    "   Expectstarttime," +
                    "   Expectendtime," +
                    "   Obstatus," +
                    "   Recordstatus," +
                    "   Attempt," +
                    "   Max_Attempt," +
                    "   Handleby_Id," +
                    "   Handlegroup_Id," +
                    "   Createdby," +
                    "   Createddate," +
                    "   Failure_Attempt," +
                    "   Comments," +
                    "   Customer_Guid," +
                    "   Localtrycount," +
                    "   Lastassigndate," +
                    "   Workorderframe_Id," +
                    "   Curstepstatus," +
                    "   Curstep_Id," +
                    "   Stepforecastdate," +
                    "   Curstep_Id_Map," +
                    "   Map_Id," +
                    "   Objectivesubstatus," +
                    "   Elitetaskstatus," +
                    "   Obflag,Maintaskstep_Id_Map,PA_TASKTYPE)" +
                    "Values" +
                    "  ('" + EliteTask_Guid + "'," +
                    "   '" + Objective_Guid + "'," +
                    "   'RE0TX1'," +
                    "   Now()," +
                    "   Date_Add(Now(), Interval 3 Day)," +
                    "   'OPEN'," +
                    "   0," +
                    "   0," +
                    "   999," +
                    "   '" + r_Staff_Id + "'," +
                    "   '" + r_Rolegroup_Id + "'," +
                    "   'SELITE'," +
                    "   Now()," +
                    "   999," +
                    "   ''," +
                    "   '" + Customer_guid + "'," +
                    "   0," +
                    "   Now()," +
                    "   ''," +
                    "   'OPEN'," +
                    "   'ONA0KD'," +
                    "   Now()," +
                    "   'ONA0KD'," +
                    "   '4CA2PP'," +
                    "   'OPEN'," +
                    "   'OPEN'," +
                    "   1,'ONA0KD','" + task_type + "')";

            // 插入Assign_His表
            String assignLog = "开户自动下发-名单ID:" + Rec_Id + "&规则ID:" + Rule_Id + "&人员控制ID:" + r_Control_Id + "&任务TaskGuid:" + EliteTask_Guid;
            log.debug(assignLog);
            custTaskDealSQL[3] = "Update Hst_Rule_Issue_Control Set Assign_Num = Assign_Num + 1, Last_Check_Time = Now()" +
                    " Where Rec_Id =  '" + r_Control_Id + "' And channel ='" + channel + "'";
            //写入顾问分配历史表
            custTaskDealSQL[4] = "insert into hst_Service_hislist (sys_guid,customer_phone,customer_guid,used_staffid,apply_time,MODIFIEDBY,context,Reviewer_staffid,Reviewer_time,appointment_time)values(UUID(),'" + mobile + "','" + Customer_guid + "','" + r_Staff_Id + "',now(),'" + 'SELITE' + "','自动下发分配','SELITE',now(),'" + appointment_time + "')";
            int[] batchSQLResult = eliteBean.updateBatch(custTaskDealSQL, "dataSource");
            log.debug("新客户入库批量处理SQL返回结果：" + batchSQLResult.toString());

            //发个短信
            apimeesg(eliteBean, Customer_guid, r_Staff_Id, r_Rolegroup_Id, mobile, channel, channels);

        } catch (Exception e) {
            log.error("新客户入库异常!", e);
            reFlag = false;
        }

        return reFlag;
    }

    /**
     * 自动发送短信
     * @param eliteBean
     * @param Customer_Guid
     * @param r_Staff_Id
     * @param r_Rolegroup_Id
     * @param mobile
     * @param channel
     * @param channels
     * @return
     */
    public static boolean apimeesg(EliteBean eliteBean, String Customer_Guid, String r_Staff_Id, String r_Rolegroup_Id, String mobile, String channel, String channels) {
        Boolean Bisflag = true;
        try {
            /**
             * @Title:
             * @Description:
             * @Param: 查找当前客户预约来源是否是 MNDS ,如果是暂停发送短信
             * @return: boolean
             * @Author: TaoHao
             * @Date: 2025/02/13
             */
            String selectSql='''select  innerentry from customer where customer_guid=?''';
            List<Map<String, Object>> selelctList = jt.queryForList(selectSql, Customer_Guid);
            if (selelctList.size()>0){
                String  select_innerentry=selelctList.get(0).get("innerentry");
                //预约来源配置表
                String InnerentryConfigSql='''select Innerentry from Pa_Innerentry ''';
                List<Map<String, Object>> InnerentryConfigList = jt.queryForList(InnerentryConfigSql);
                for (int P=0;P<InnerentryConfigList.size();P++) {
                    String configinnerentry=InnerentryConfigList.get(P).get("Innerentry");
                    if (configinnerentry.equals(select_innerentry)){
                        log.debug("【来自的客户预约来源限制不发即推短信】"+mobile+"【预约来源】:"+configinnerentry);
                        return false;
                    }
                }

            }




            /**
            * @Title:
            * @Description:
            * @Param: 查询该手机号是否黑名单
            * @return: boolean
            * @Author: CC
            * @Date: 2024/6/14
            */
            String isblacksql = "select PHONENO   from SPECIALPHONE where PHONENO=? and STATUS='1' ";

            List<Map<String, Object>> selelctBlackList = jt.queryForList(isblacksql, mobile);

            if (selelctBlackList.size()>0){
                log.debug("【黑名单手机不允许发送短信,查询到该手机号是黑名单：】"+mobile);
                return false;
            }

            if (KHDBConfig.getParam(eliteBean, "MSG-KG") == "OPEN") {
                List StaffList = eliteBean.chaxun_sql("Select If(Stringfield4 = 'WX', Wechat_url, Null) As Wechat_url, Weixinid, Mobile From Staff Where Staff_Id = '" + r_Staff_Id + "' limit 1 ", "dataSource");
                String wxURL = StaffList.get(0).get("Wechat_url");
                String wxID = StaffList.get(0).get("Weixinid");
                if (KHUtil.isNull(wxURL) && KHUtil.isNull(wxID)) {
                    log.warn("该坐席未配置任务有效微信信息", StaffList.toString());
                    return false;
                } else {

                    List<Map<String, String>> sendList = getSendConfig(eliteBean, channel, channels,mobile);
                    for (int i = 0; i < sendList.size(); i++) {
                        Map<String, String> ConfigMap = sendList.get(i);
                        String sendTime = ConfigMap.get("sendTime");
                        String Doctemplate_Id = ConfigMap.get("sendTemplate_Id");

                        // 生成待发送短信的Guid
                        String Documentout_Guid = KHUtil.getRandomString(36);
                        String smsParam=null;

                        if(channel.equals("ssyzhibo")){
                            smsParam=KHDBConfig.getParam(eliteBean, "HYW-DX");
                            log.debug("短信参数："+smsParam);
                        }else{
                            // 如果坐席有二维码获取短链接否则直接发微信ID
                            smsParam = KHUtil.isNull(wxURL) ? wxID : wxURL;
                            // 再次检查是否获取到短链接，如果没有用微信ID
                            smsParam = KHUtil.isNull(smsParam) ? wxID : smsParam;
                        }
                        List dmpList = eliteBean.chaxun_sql("Select Documentpath, Doctemplate2 From Doctemplate Where Doctemplate_Id = '" + Doctemplate_Id + "' Limit 1", "dataSource");
                        if (dmpList.size() <= 0) {
                            log.warn("未查询到短信模板信息,放弃发送");
                            continue;

                        } else {
                            Map dmpMap2 = dmpList.get(0);
                            String Documentpath = dmpMap2.get("Documentpath");
                            String Doctemplate2 = dmpMap2.get("Doctemplate2");
                            String[] Doctemplate2_Arr = Doctemplate2.split(",");
                            if (Doctemplate2_Arr.length != 1) {
                                log.warn("查询到短信模板参数个数超出预期,放弃发送");
                                continue;
                            } else {
                                // 生成短信发送ID
                                String   MsgID = System.currentTimeMillis()+new Random().nextInt(99999).toString();
                                log.debug("MSG_ID"+MsgID);
                                // 生成短信发送参数
                                String Documentpath2 = "{\"" + Doctemplate2 + "\":\"" + smsParam + "\"}"
                                // 生成短信发送内容
                                Documentpath = Documentpath.replace("[\$" + Doctemplate2 + "\$]", smsParam);

                                // 坐席二维码海报链接
                                String Poster_Url = KHUtil.isNull(wxURL) ? "" : WXCallBackURL + wxURL;

                                log.debug("短链接地址：" + smsParam + " 短内容：" + Documentpath + " 参数内容：" + Documentpath2);

                                String insertSQL = '''Insert Into Documentout
                                              (Documentout_Guid,
                                               Doctype,
                                               Status,
                                               Trialcount,
                                               Trialcountlimit,
                                               Documentpath,
                                               Documentpath2,
                                               Handleby_Id,
                                               Handlegroup_Id,
                                               Customer_Guid,
                                               Createdby,
                                               Createddate,
                                               Expectedstartdate,
                                               Documentto,
                                               Lasttrydatetime,
                                               Sub_Doctype,
                                               Msgid,
                                               Template_Id,
                                               Poster_Guid,
                                               Poster_Url,
                                               Send_Type,
                                               Stringfield1)
                                            Values
                                              (?,
                                               3,
                                               0,
                                               0,
                                               3,
                                               ?,
                                               ?,
                                               ?,
                                               ?,
                                               ?,
                                               ?,
                                               Now(),
                                               ?,
                                               ?,
                                               Null,
                                               '电销预约自动短信',
                                               ?,
                                               ?,
                                               ?,
                                               ?,
                                               '3',
                                               ?)''';

                                int insertNum = jt.update(insertSQL, Documentout_Guid, Documentpath, Documentpath2, r_Staff_Id, r_Rolegroup_Id, Customer_Guid, "SELITE", sendTime, mobile, MsgID, Doctemplate_Id, Documentout_Guid, Poster_Url, Poster_Url);

                                log.debug("批量发送海报短信：" + Documentout_Guid + " 成功 操作数据：" + insertNum);

                            }
                        }

                    }
                }

            } else {
                Bisflag = false;
                log.debug("自动发送短信关闭");
            }
        } catch (Exception e) {
            Bisflag = false;
            log.error("自动发送短信异常!", e);
        }
        return Bisflag;
    }

    // 获取系统配置的短信发送时间+模板信息
    public static List getSendConfig(EliteBean eliteBean, String Channel_1, String Channel_2,String mobile) {

        List sendList = new ArrayList();

        try {
            String getConfigSQL = '''Select R1.Rec_Guid,
                                           R1.Channel_1,
                                           R1.Channel_2,
                                           R3.Freedate,
                                           R3.Next_Freedate,
                                           R1.Send_Time,
                                           Case
                                             When R3.Datetype = 'PA0001' And Now() Between Concat(Date_Format(Now(), '%Y-%m-%d '), Substring_Index(R1.Send_Time, '-', 1)) And
                                                  Concat(Date_Format(Now(), '%Y-%m-%d '), Substring_Index(R1.Send_Time, '-', -1)) Then
                                              Concat('交易时间#', Date_Format(Now(), '%Y-%m-%d %H:%i:%s'))
                                             Else
                                              Concat('非交易时间#',
                                                     If(Now() Between Concat(Date_Format(Now(), '%Y-%m-%d '), '09:00:00') And Concat(Date_Format(Now(), '%Y-%m-%d '), '22:00:00'),
                                                        Date_Format(Now(), '%Y-%m-%d %H:%i:%s'),
                                                        If(Now() Between Concat(Date_Format(Now(), '%Y-%m-%d '), '00:00:00') And Concat(Date_Format(Now(), '%Y-%m-%d '), '09:00:00'),
                                                           Concat(Date_Format(Now(), '%Y-%m-%d '), '09:00:00'),
                                                           Concat(Date_Format(Date_Add(Now(), Interval 1 Day), '%Y-%m-%d '), '09:00:00'))),
                                                     '#',
                                                     Concat(Date_Format(Date_Add(Now(), Interval If(R2.Delay_Day = 0, 0, R3.Datediff) Day), '%Y-%m-%d '), '12:00:00'))
                                           End As Datetime_Flag,
                                           R1.In_Doctemplate_Id,
                                           R1.Out_Doctemplate_Id,
                                           R1.Out_Next_Doctemplate_Id,
                                           R1.Remark,
                                           Case
                                             When R1.Channel_1 = \'''' + Channel_1 + '''\' And R1.Channel_2 = \'''' + Channel_2 + '''\' Then
                                              1
                                             When R1.Channel_2 Is Null Or R1.Channel_2 = '' Then
                                              2
                                             Else
                                              98
                                           End As Levelno
                                      From Pa_Auto_Sendsms_Config R1
                                      Left Join (Select R1.Rec_Guid,
                                                        R1.Weekday,
                                                        R1.Weekday_Desc,
                                                        R1.Start_Time,
                                                        R1.End_Time,
                                                        R1.Delay_Day,
                                                        R1.Delay_Starttime,
                                                        R1.Remark
                                                   From Pa_Auto_Sendsms_Non_Work_Config R1
                                                  Where R1.Del_Flag = 0
                                                    And R1.Weekday = 'PA0000'
                                                    And Concat(Date_Format(Now(), '%Y-%m-%d '), R1.Start_Time) <= Now()
                                                    And Concat(Date_Format(Date_Add(Now(), Interval If(R1.End_Time = '00:00:00', 1, 0) Day), '%Y-%m-%d '), R1.End_Time) > Now() Limit 1) R2
                                        On 1 = 1
                                      Left Join (Select R1.Freedate, R1.Datetype, R1.Next_Freedate, Datediff(R1.Next_Freedate, R1.Freedate) As Datediff
                                                  From (Select R0.d_Date As Freedate,
                                                               If(R1.Datetype = Null Or R1.Datetype = '', 'PA0000', R1.Datetype) As Datetype,
                                                               Min(Date_Format(R2.Freedate, '%Y-%m-%d')) As Next_Freedate
                                                          From Dim_d_Day R0
                                                          Left Join c_Freedate R1
                                                            On R0.d_Date = Date_Format(R1.Freedate, '%Y-%m-%d')
                                                           And R1.Datetype In ('PA0001', 'PA0000')
                                                          Left Join c_Freedate R2
                                                            On R1.Freedate < R2.Freedate
                                                           And R2.Datetype = 'PA0001'
                                                         Where R0.d_Date = Date_Format(Now(), '%Y-%m-%d')
                                                         Group By Date_Format(R1.Freedate, '%Y-%m-%d'),R0.d_Date,R1.Datetype ) R1 Limit 1) R3
                                        On 1 = 1
                                     Where R1.Channel_1 != 'ALL'
                                       And R1.Del_Flag = 0
                                       And R1.Config_Type = '电销预约自动短信'
                                       And R1.Channel_1 = \'''' + Channel_1 + '''\'
                                    Union All
                                    Select R1.Rec_Guid,
                                           R1.Channel_1,
                                           R1.Channel_2,
                                           R3.Freedate,
                                           R3.Next_Freedate,
                                           R1.Send_Time,
                                           Case
                                             When R3.Datetype = 'PA0001' And Now() Between Concat(Date_Format(Now(), '%Y-%m-%d '), Substring_Index(R1.Send_Time, '-', 1)) And
                                                  Concat(Date_Format(Now(), '%Y-%m-%d '), Substring_Index(R1.Send_Time, '-', -1)) Then
                                              Concat('交易时间#', Date_Format(Now(), '%Y-%m-%d %H:%i:%s'))
                                             Else
                                              Concat('非交易时间#',
                                                     If(Now() Between Concat(Date_Format(Now(), '%Y-%m-%d '), '09:00:00') And Concat(Date_Format(Now(), '%Y-%m-%d '), '22:00:00'),
                                                        Date_Format(Now(), '%Y-%m-%d %H:%i:%s'),
                                                        If(Now() Between Concat(Date_Format(Now(), '%Y-%m-%d '), '00:00:00') And Concat(Date_Format(Now(), '%Y-%m-%d '), '09:00:00'),
                                                           Concat(Date_Format(Now(), '%Y-%m-%d '), '09:00:00'),
                                                           Concat(Date_Format(Date_Add(Now(), Interval 1 Day), '%Y-%m-%d '), '09:00:00'))),
                                                     '#',
                                                     Concat(Date_Format(Date_Add(Now(), Interval If(R2.Delay_Day = 0, 0, R3.Datediff) Day), '%Y-%m-%d '),  '12:00:00'))
                                           End As Datetime_Flag,
                                           R1.In_Doctemplate_Id,
                                           R1.Out_Doctemplate_Id,
                                           R1.Out_Next_Doctemplate_Id,
                                           R1.Remark,
                                           99 As Levelno
                                      From Pa_Auto_Sendsms_Config R1
                                      Left Join (Select R1.Rec_Guid,
                                                        R1.Weekday,
                                                        R1.Weekday_Desc,
                                                        R1.Start_Time,
                                                        R1.End_Time,
                                                        R1.Delay_Day,
                                                        R1.Delay_Starttime,
                                                        R1.Remark
                                                   From Pa_Auto_Sendsms_Non_Work_Config R1
                                                  Where R1.Del_Flag = 0
                                                    And R1.Weekday = 'PA0000'
                                                    And Concat(Date_Format(Now(), '%Y-%m-%d '), R1.Start_Time) <= Now()
                                                    And Concat(Date_Format(Date_Add(Now(), Interval If(R1.End_Time = '00:00:00', 1, 0) Day), '%Y-%m-%d '), R1.End_Time) > Now() Limit 1) R2
                                        On 1 = 1
                                      Left Join (Select R1.Freedate, R1.Datetype, R1.Next_Freedate, Datediff(R1.Next_Freedate, R1.Freedate) As Datediff
                                                  From (Select R0.d_Date As Freedate,
                                                               If(R1.Datetype = Null Or R1.Datetype = '', 'PA0000', R1.Datetype) As Datetype,
                                                               Min(Date_Format(R2.Freedate, '%Y-%m-%d')) As Next_Freedate
                                                          From Dim_d_Day R0
                                                          Left Join c_Freedate R1
                                                            On R0.d_Date = Date_Format(R1.Freedate, '%Y-%m-%d')
                                                           And R1.Datetype In ('PA0001', 'PA0000')
                                                          Left Join c_Freedate R2
                                                            On R1.Freedate < R2.Freedate
                                                           And R2.Datetype = 'PA0001'
                                                         Where R0.d_Date = Date_Format(Now(), '%Y-%m-%d')
                                                         Group By Date_Format(R1.Freedate, '%Y-%m-%d'),R0.d_Date,R1.Datetype ) R1 Limit 1) R3
                                        On 1 = 1
                                     Where R1.Channel_1 = 'ALL'
                                       And R1.Del_Flag = 0
                                       And R1.Config_Type = '电销预约自动短信'
                                     Order By Levelno Limit 1''';

            List getConfigList = eliteBean.chaxun_sql(getConfigSQL, "dataSource");
            log.debug("获取短信发送规则配置：" + getConfigList.toString());

            if (getConfigList.size() > 0) {
                String Datetime_Flag = getConfigList.get(0).get("Datetime_Flag");
                String In_Doctemplate_Id = getConfigList.get(0).get("In_Doctemplate_Id");
                String Out_Doctemplate_Id = getConfigList.get(0).get("Out_Doctemplate_Id");
                String Out_Next_Doctemplate_Id = getConfigList.get(0).get("Out_Next_Doctemplate_Id");
                String Levelno = getConfigList.get(0).get("Levelno");

                String[] Datetime_Arr = Datetime_Flag.split("#");

                if (Datetime_Arr[0] == "交易时间") {
                    Map<String, String> ConfigMap = new HashMap<String, String>();
                    ConfigMap.put("sendTime", Datetime_Arr[1]);
                    ConfigMap.put("sendTemplate_Id", In_Doctemplate_Id);
                    sendList.add(ConfigMap);

                } else if (Datetime_Arr[0] == "非交易时间") {
                    Map<String, String> ConfigMap = new HashMap<String, String>();
                    ConfigMap.put("sendTime", Datetime_Arr[1]);
                    ConfigMap.put("sendTemplate_Id", Out_Doctemplate_Id);
                    sendList.add(ConfigMap);



                    //手机是否开户与注册
                    List userinfoList=eliteBean.chaxun_sql("select investorid from dws.paqh_userinfo where mobile='"+mobile+"'","dataSource");
                    List registerList=eliteBean.chaxun_sql("select f_register_time From  dwd.yun_member where f_register_time !='null' and f_phone='"+mobile+"'","dataSource");
                    if (userinfoList.size()<=0&&registerList.size()<=0){
                        Map<String, String> NextConfigMap = new HashMap<String, String>();
                        NextConfigMap.put("sendTime", Datetime_Arr[2]);
                        NextConfigMap.put("sendTemplate_Id", Out_Next_Doctemplate_Id);
                        sendList.add(NextConfigMap);
                    }else{
                        log.debug("该手机号已经开户或注册了",mobile);

                    }
                } else {
                    log.warn("未知的规则配置类型", getConfigList.toString());

                }
            } else {
                log.warn("未查询到任何规则", getConfigList.toString());

            }

        } catch (Exception e) {
            log.error("查询处理自动短信发送规则配置异常!", e);
        }

        return sendList;
    }

    public static String getWXSURL(EliteBean eliteBean, String Documentout_Guid) {
        String url_link = "";
        try {
            // 调用获取短链接
            JSONObject postBody = new JSONObject();
            postBody.put("path", "/pages/index/index");
            postBody.put("is_expire", true);
            postBody.put("expire_type", 1);
            postBody.put("expire_interval", 30);

            postBody.put("query", "recid=" + Documentout_Guid + "&expand=AUTOSEND");

            String postResult = Util.doHttpPost(API_OuterDomain + "/apiWXMiniPURL", postBody.toString(), "UTF-8", 12000, 0);

            JSONObject postResultJson = JSONObject.fromObject(postResult);

            int errcode = postResultJson.getInt("errcode");
            if (errcode == 0) {
                // 获取短链接成功
                url_link = postResultJson.getString("url_link");
            } else {
                log.warn("自动短信获取小程序返回结果未知!", postResultJson.toString());
            }
        } catch (Exception e) {
            log.error("自动短信获取小程序短链接异常!", e);
        }
        return url_link;

    }

/**
 * 平安期货-人员更换
 * @param eliteBean
 * @param r_Control_Id
 * @param r_Rolegroup_Id
 * @param r_Staff_Id
 * @param Customer_guid
 * @param channel
 * @return
 */
    public static boolean UpdateStaff(EliteBean eliteBean, String r_Control_Id, String r_Rolegroup_Id,
                                      String r_Staff_Id,String Customer_guid,String channel) {
        boolean reFlag=true;
        try{
            String[] custTaskUpdateSQL = new String[4];
            log.debug("更换人员ID："+r_Staff_Id+"更换GUID:"+Customer_guid+"更换人员组别："+r_Rolegroup_Id);
            //1.更新 elitetask表Hanleby_ID,Handlegroup_Id
            custTaskUpdateSQL[0] = "Update elitetask  Set Handleby_Id='"+r_Staff_Id+"',Handlegroup_Id='"+r_Rolegroup_Id+"'   Where Customer_Guid='" + Customer_guid + "' and MAP_ID='4CA2PP'";
            //2.更新 objective表Hanleby_ID,Handlegroup_Id
            custTaskUpdateSQL[1] = "Update objective Set Handleby_Id='"+r_Staff_Id+"',Handlegroup_Id='"+r_Rolegroup_Id+"'     Where  Customer_Guid='" + Customer_guid + "' and MAP_ID='4CA2PP'";
            //3.更新下发控制表
            custTaskUpdateSQL[2] = "Update Hst_Rule_Issue_Control Set Assign_Num = Assign_Num + 1, Last_Check_Time = Now() Where Rec_Id =  '" + r_Control_Id + "' And channel ='" + channel + "'";
            //写入顾问分配历史表
            custTaskUpdateSQL[3] = "insert into hst_Service_hislist (sys_guid,customer_guid,used_staffid,apply_time,MODIFIEDBY,context,Reviewer_staffid,Reviewer_time)values(UUID(),'" + Customer_guid + "','" + r_Staff_Id + "',now(),'" + 'SELITE' + "','电销人员更换','SELITE',now())";
            int[] batchSQLUpdateResult = eliteBean.updateBatch(custTaskUpdateSQL, "dataSource");
            log.debug("[更换人员更新结果集返回]:"+batchSQLUpdateResult);
        } catch (Exception e) {
            log.error("更新人员异常!", e);
            reFlag = false;
        }

        return reFlag;
    }

    /**
     * 下发规则数据兜底
     * @param eliteBean
     * @param r_Rolegroup_Id
     * @param r_Staff_Id
     * @param channel
     * @return
     */
    public static Map<String, String>  NullChannelRule(EliteBean eliteBean, String channel,String appointment_time) {

        boolean reFlag=true;
        Map<String, String> map= new HashMap<String, String>();
        try{
            String r_Rolegroup_Id=null;
            String r_Staff_Id=null;
            String r_Control_Id=null;
            String rule_id=KHDBConfig.getParam(eliteBean, "DD_ID");
            log.debug("兜底规则rule_id"+rule_id);
            String ruleCheckSQL1 = "Select Rule_Id, Rule_Name, Rolegroup, Staff_Id, Order_Adjustment,yy_starttime,yy_endttime From " +
                    "Hst_Rule_Issue Where Rule_State = 1 and department_flag=2 and rule_id='"+rule_id+"' Limit 1";
            List  ruleList  = eliteBean.chaxun_sql(ruleCheckSQL1, "dataSource");
            if (ruleList.size()>0){
                Map ruleCheckMap2 = ruleList.get(0);
                rule_id = ruleCheckMap2.get("Rule_Id");
                String Rule_Name = ruleCheckMap2.get("Rule_Name");
                String Rolegroup = ruleCheckMap2.get("Rolegroup");
                String Staff_Id = ruleCheckMap2.get("Staff_Id");
                String yy_starttime = ruleCheckMap2.get("yy_starttime")== null ? '' : ruleCheckMap2.get("yy_starttime");
                String yy_endttime = ruleCheckMap2.get("yy_endttime")== null ? '' : ruleCheckMap2.get("yy_endttime");
                String Order_Adjustment = ruleCheckMap2.get("Order_Adjustment");
                log.debug("[输出获取兜底规则信息] 规则ID:" + rule_id + " 规则名:" + Rule_Name + " 下发组:" + Rolegroup + " 下发人:" + Staff_Id + " 优先级:" + Order_Adjustment);
                // 初始化规则下发
                ruleAssignInit(eliteBean, rule_id, channel,appointment_time,yy_starttime,yy_endttime);
                // 取当前名单下发最少的坐席（不支持一人多组）
                String getAssginStaffSQL = "Select R1.Rec_Id, R2.Rolegroup_Id, R1.Staff_Id" +
                        "  From Hst_Rule_Issue_Control R1" +
                        "  Left Join Staffrolegroup R2" +
                        "    On R1.Staff_Id = R2.Staff_Id" +
                        "  Left Join Staff R3" +
                        "    On R1.Staff_Id = R3.Staff_Id" +
                        " Where Rule_Id = " + rule_id + "" +
                        "   And Assign_Date = Date_Format(Now(), '%Y-%m-%d')" +
                        "   And channel='" + channel + "'  " +
                        "   And R2.Rolegroup_id='" + Rolegroup + "' " +
                        " Order By Assign_Num,Staff_Id Limit 1";
                List getAssginStaffList1 = eliteBean.chaxun_sql(getAssginStaffSQL, "dataSource");
                if (getAssginStaffList1.size() > 0) {
                    Map getAssginStaffMap2 = getAssginStaffList1.get(0);
                    r_Control_Id = getAssginStaffMap2.get("Rec_Id");
                    r_Rolegroup_Id = getAssginStaffMap2.get("Rolegroup_Id");
                    r_Staff_Id = getAssginStaffMap2.get("Staff_Id");
                    log.debug("[当前兜底名单下发人信息] Rolegroup_Id:" + r_Rolegroup_Id + " Staff_Id:" + r_Staff_Id);

                } else {
                    r_Rolegroup_Id = '';
                    r_Staff_Id = '';
                    log.debug("[当前兜底名单下发人信息] 无组别人员 默认不分配 进入未分配数据池！");

                }
            }else{
                //找不到规则,先值为空
                r_Rolegroup_Id='';
                r_Staff_Id='';
                log.debug("[当前兜底名单下发人信息] 无组别人员 默认不分配 进入未分配数据池！");
            }
            map.put("r_Rolegroup_Id",r_Rolegroup_Id);
            map.put("r_Control_Id",r_Control_Id);
            map.put("r_Staff_Id",r_Staff_Id);
        } catch (Exception e) {
            log.error("兜底下发规则人员异常!", e);
        }
        return map;


    }



}
